package com.yc.api.d1030;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Map;

import org.junit.Test;

import com.yc.api.d1026.DBHelper;
// page 是静态内部类
import com.yc.api.d1026.DBHelper.Page;
import com.yc.api.d1026.DBHelper.SqlHandler;

import org.junit.Assert;

public class DBHelperTest {

	@Test // 注解 : 标注当前方法是一个测试方法
	public void test1() throws SQLException {

		String sql = "select sysdate now from dual";

		Map<String, Object> map = DBHelper.selectOneMap(sql);

		System.out.println(map);
		System.out.println(map.get("NOW").getClass());

		// 断言 该 map 不为空
		Assert.assertNotNull(map);
		// 断言 该 map 中包含一个 名字为 NOW 日期字段
		Assert.assertTrue(map.get("NOW") instanceof java.util.Date);

	}

	// 先写测试, 再写程序 ==> 测试驱动开发 ==> 极限编程
	@Test
	public void test2() throws SQLException {

		String sql = "select * from emp where empno > ? and mgr > ?";
		Page<Map<String, Object>> page = DBHelper.selectPage(sql, 1, 5, 1000, 2000);
		// 断言页数是 1
		Assert.assertEquals(1, page.getPage());
		// 断言每页行数页数是 5
		Assert.assertEquals(5, page.getSize());
		// 断言当前页数据行数
		Assert.assertEquals(5, page.getData().size());

		page = DBHelper.selectPage(sql, 3, 5, 1000, 2000);
		// 断言页数是 3
		Assert.assertEquals(3, page.getPage());
		// 断言每页行数页数是 5
		Assert.assertEquals(5, page.getSize());
		// 断言当前页数据行数 , 只有 4行
		Assert.assertEquals(3, page.getData().size());
	}

	@Test
	public void test3() throws SQLException {
		
		// 初始化emp 和 dept 的测试数据
		DBHelper.update("delete dept where deptno = ? ", 43);
		DBHelper.update("delete emp where deptno = ? or deptno is null ", 43);
		
		// 新增一个部门, 同时添加该部门的一个经理的员工, 员工姓名叫"待入职"
		DBHelper.execute(connection -> {
			try {
				PreparedStatement ps1 = connection.prepareStatement("insert into dept values(?,?,?)");
				PreparedStatement ps2 = connection.prepareStatement("insert into emp values(?,?,?,?,?,?,?,?)");
				ps1.setInt(1, 43);
				ps1.setString(2, "XXX部门");
				ps1.setString(3, "XXX市");
				ps1.executeUpdate();
				ps2.setInt(1, 8000);
				
				int i = 1/0;  // 制造一个异常
				
				ps2.setString(2, "待入职");
				ps2.setObject(3, null);
				ps2.setObject(4, null);
				ps2.setObject(5, null);
				ps2.setObject(6, null);
				ps2.setObject(7, null);
				ps2.setInt(8, 43);
				ps2.executeUpdate();
			} catch (SQLException e) {
				throw new RuntimeException("新增部门失败", e);
			}
			return null;
		});
		
		String testSql1 = "select * from dept where deptno=?";
		Map<String, Object> dept = DBHelper.selectOneMap(testSql1, 43);
		// 由于执行过程出现异常, 那么当前查询 dpet == null
		Assert.assertNull(dept);
		
		DBHelper.execute(connection -> {
			try {
				PreparedStatement ps1 = connection.prepareStatement("insert into dept values(?,?,?)");
				PreparedStatement ps2 = connection.prepareStatement("insert into emp values(?,?,?,?,?,?,?,?)");
				ps1.setInt(1, 43);
				ps1.setString(2, "XXX部门");
				ps1.setString(3, "XXX市");
				ps1.executeUpdate();
				ps2.setInt(1, 8000);
				ps2.setString(2, "待入职");
				ps2.setObject(3, null);
				ps2.setObject(4, null);
				ps2.setObject(5, null);
				ps2.setObject(6, null);
				ps2.setObject(7, null);
				ps2.setInt(8, 43);
				ps2.executeUpdate();
			} catch (SQLException e) {
				throw new RuntimeException("新增部门失败", e);
			}
			return null;
		});
		
		dept = DBHelper.selectOneMap(testSql1, 43);
		Assert.assertNotNull(dept);
		
		// 验证新员工
		String testSql2 = "select * from emp where deptno=?";
		Map<String, Object> emp = DBHelper.selectOneMap(testSql2, 43);
		Assert.assertNotNull(emp);
		Assert.assertEquals("待入职",emp.get("ENAME"));
	}

}
